﻿

CREATE proc [dbo].[GetPersons]
@PersKey	int = null,
@Name		nvarchar(50) = null
as
begin
	set nocount on

	select	p.PersKey,
			coalesce(p.PersTS,'')		'PersTS',
			p.Änderung, 
			coalesce(p.Zuname,'')		'Zuname', 
			coalesce(p.Vorname,'')		'Vorname', 
			coalesce(p.Geschlecht,0)	'Geschlecht', 
			p.TitelKey1, 
			p.TitelKey2, 
			p.TitelKey3,
			coalesce(p.Adresse, '')		'Adresse',
			p.EPLZ, 
			coalesce(p.Straße, '')		'Straße',
			plz.PLZ 'Plz',
			plz.Ort,
			p.VorwahlKey, 
			coalesce(p.Mobil, '')		'Mobil',
			coalesce(vw.Vorwahl + ' / ' + p.Mobil, '')		'MobilFull',
            coalesce(p.PrivTelefon, '')	'PrivTelefon',
			coalesce(p.PrivFax, '')		'PrivFax',
			p.MailingSperre, 
			coalesce(p.Notiz, '')		'Notiz',
			p.Mitarbeiter, 
			rtrim(coalesce(p.Kürzel, '')) 'Kürzel',
			coalesce(p.eMail, '')		'eMail',
			p.CreatedOn,
			p.CreatedBy,
			p.LastChangedOn,
			p.LastChangedBy
	from	dbo.Person p
			left join dbo.Postleitzahl plz on
				plz.EPLZ = p.EPLZ
			left join dbo.Vorwahl vw on
				vw.VorwahlKey = p.VorwahlKey
	where	(p.PersKey = @PersKey
				or @PersKey is null)
			and ((p.Zuname like @Name or p.Vorname like @Name) or @Name is null)
	order by p.Zuname, p.Vorname
end
GO
GRANT EXECUTE
    ON OBJECT::[dbo].[GetPersons] TO PUBLIC
    AS [dbo];

